function createString = SQL_TableCreateString(whatTable)
% SQL_TablecreateString
%
% Determine the appropriate mySQL CREATE TABLE statement to use to create a given
% table, identified by the input string, whatTable.

% ------------------------------------------------------------------------------
% Copyright (C) 2020, Ben D. Fulcher <ben.d.fulcher@gmail.com>,
% <http://www.benfulcher.com>
%
% If you use this code for your research, please cite the following two papers:
%
% (1) B.D. Fulcher and N.S. Jones, "hctsa: A Computational Framework for Automated
% Time-Series Phenotyping Using Massive Feature Extraction, Cell Systems 5: 527 (2017).
% DOI: 10.1016/j.cels.2017.10.001
%
% (2) B.D. Fulcher, M.A. Little, N.S. Jones, "Highly comparative time-series
% analysis: the empirical structure of time series and their methods",
% J. Roy. Soc. Interface 10(83) 20130048 (2013).
% DOI: 10.1098/rsif.2013.0048
%
% This work is licensed under the Creative Commons
% Attribution-NonCommercial-ShareAlike 4.0 International License. To view a copy of
% this license, visit http://creativecommons.org/licenses/by-nc-sa/4.0/ or send
% a letter to Creative Commons, 444 Castro Street, Suite 900, Mountain View,
% California, 94041, USA.
% ------------------------------------------------------------------------------

switch whatTable
case 'Operations'
    createString = ['CREATE TABLE Operations ' ...
        '(op_id INTEGER NOT NULL AUTO_INCREMENT, ' ... % Unique integer identifier
        'Name VARCHAR(255), ' ... % Unique name for the operation
        'Code VARCHAR(255), ' ... % Code to execute, or Master to retrieve from
        'Keywords VARCHAR(255), ' ... % Comma separated keyword metadata ...
        'MasterLabel VARCHAR(255), ' ... % Label of master code ...
        'mop_id INTEGER, ' ... % m_op id
        'LastModified TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, ' ... % Last modified
        'PRIMARY KEY (op_id), ' ...  % sets primary key as op_id
        'FOREIGN KEY (mop_id) REFERENCES MasterOperations(mop_id) ON DELETE CASCADE ON UPDATE CASCADE)'];

case 'OperationCode'
    createString = ['CREATE TABLE OperationCode ' ...
        '(c_id INTEGER NOT NULL AUTO_INCREMENT, ' ...
        'CodeName VARCHAR(255), ' ...
        'Description TEXT, ' ...
        'LicenseType INTEGER UNSIGNED, ' ...
        'PRIMARY KEY (c_id))'];

case 'TimeSeries'
    createString = ['CREATE TABLE TimeSeries ' ...
        '(ts_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, ' ... % Unique integer identifier
        'Name VARCHAR(255) NOT NULL, ' ... % Name (e.g., filename) of the time series
        'Keywords VARCHAR(255), ' ... % Comma-delimited keywords assigned to the time series
        'Length INTEGER UNSIGNED, ' ... % Length of the time series
        'Data MEDIUMTEXT, ' ... % Add data into database :-O
        'LastModified TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP)']; % Time stamp of when the time series was last modified/inserted
        % 'Quantity VARCHAR(255), ' ... % The quantity measured across time
        % 'Unit VARCHAR(50), ' ... % The physical unit of the quantity measured
        % 'SamplingRate VARCHAR(50), ' ... % Sampling rate of the time series
        % 'Description TEXT, ' ... % More information about this specific time series

case 'MasterOperations'
    createString = ['CREATE TABLE MasterOperations ' ...
        '(mop_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, ' ... % Unique integer identifier
        'MasterLabel VARCHAR(255), ' ... % Name given to master code file
        'MasterCode VARCHAR(255), ' ... % Code to execute
        'NPointTo INTEGER UNSIGNED, ' ... % Number of children
        'LastModified TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP)']; % Time stamp of when entry was last modified

case 'OperationKeywords'
    createString = ['CREATE TABLE OperationKeywords ' ...
        '(opkw_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, ' ...
        'Keyword VARCHAR(255), ' ...
        'NumOccur INTEGER)'];
        % ', ' ...
%         'PercentageCalculated FLOAT, ' ...
%         'PercentageGood FLOAT, ' ...
%         'MeanCalcTime FLOAT)'];

case 'OpKeywordsRelate'
    createString = ['CREATE TABLE OpKeywordsRelate ' ...
        '(op_id INTEGER,' ...
        'opkw_id INTEGER, '  ...
        'FOREIGN KEY (opkw_id) REFERENCES OperationKeywords (opkw_id) ON DELETE CASCADE ON UPDATE CASCADE, ' ...
        'FOREIGN KEY (op_id) REFERENCES Operations (op_id) ON DELETE CASCADE ON UPDATE CASCADE)'];

case 'TimeSeriesKeywords'
    createString = ['CREATE TABLE TimeSeriesKeywords ' ...
        '(tskw_id INTEGER AUTO_INCREMENT PRIMARY KEY, ' ... % Unique identifier for each keyword
        'Keyword varchar(50), ' ... % The keyword
        'NumOccur INTEGER UNSIGNED, ' ... % Number of time series with this keyword
        'LastModified TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP)'];

case 'TsKeywordsRelate'
    createString = ['CREATE TABLE TsKeywordsRelate ' ...
        '(ts_id INTEGER, ' ...
        'tskw_id INTEGER, ' ...
        'FOREIGN KEY (tskw_id) REFERENCES TimeSeriesKeywords(tskw_id) ON DELETE CASCADE ON UPDATE CASCADE, ' ...
        'FOREIGN KEY (ts_id) REFERENCES TimeSeries(ts_id) ON DELETE CASCADE ON UPDATE CASCADE)'];

case 'Results'
    createString = ['CREATE TABLE Results ' ...
        '(ts_id integer, ' ...
        'op_id INTEGER, ' ...
        'Output DOUBLE, ' ...
        'QualityCode INTEGER UNSIGNED, ' ...
        'CalculationTime FLOAT, ' ...
        'LastModified TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, ' ...
        'FOREIGN KEY (ts_id) REFERENCES TimeSeries(ts_id) ON DELETE CASCADE ON UPDATE CASCADE, ' ...
        'FOREIGN KEY (op_id) REFERENCES Operations(op_id) ON DELETE CASCADE ON UPDATE CASCADE, ' ...
        'INDEX element (ts_id,op_id))'];

case 'GitInfo'
    createString = ['CREATE TABLE GitInfo ' ...
                    '(branch VARCHAR(255), ' ...
                    'hash VARCHAR(255), ' ...
                    'remote VARCHAR(255), ' ...
                    'url VARCHAR(255))'];

otherwise
    error('Unknown table ''%s''',whatTable)
end

end
